﻿//*** Guidance generated code ***//
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Diagnostics.Contracts;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Data;
using System.Data.Common;
using System.Runtime.Serialization.Formatters.Binary;
using System.Text;

using ProjectBase.Core;
using ProjectBase.Data;
using ProjectBase.Data.Db;

using Survey.Entity.Services;
using Survey.Common.Data.Services;

namespace Survey.Data.Services.Db
{
    public class UserProfileDao : ReadDbDao<UserProfile, int, UserProfileFilter, IDbCommand>, IUserProfileDao
    {
        #region members
        DbProviderFactory _dbProviderFactory;
        string _connectionStringName = "ProfileProvider";
        static readonly BinaryFormatter BinaryFormatter = new BinaryFormatter();
        const string CommandTextSelect = " SELECT TOP (@MaxRows) * FROM [dbo].[ServicesUserProfile] ";

        const string CommandTextInsert = @"
            INSERT INTO [dbo].[ServicesUserProfile]
                    ([UserName], [ApplicationName], [IsAnonymous], [LastActivityDate], [LastUpdateDate], [Session])
             VALUES (@UserName,  @ApplicationName,  @IsAnonymous,  @LastActivityDate,  @LastUpdateDate,  @Session)
            ;
            Set @UserProfileId = @@IDENTITY ";

        const string CommandTextUpdate = @"
            UPDATE [dbo].[ServicesUserProfile]
                SET LastActivityDate = @LastActivityDate
            FROM [dbo].[ServicesUserProfile]
            WHERE UserProfileId = @UserProfileId; ";

        const string CommandTextUpdateFull = @"
            UPDATE [dbo].[ServicesUserProfile]
                SET LastActivityDate = @LastActivityDate
                  , LastUpdateDate = @LastUpdateDate
                  , Session = @Session
            FROM [dbo].[ServicesUserProfile]
            WHERE UserProfileId = @UserProfileId; ";

        const string CommandTextDelete = " DELETE FROM [dbo].[ServicesUserProfile] WHERE UserProfileId = @UserProfileId ";
        #endregion members

        #region Get UserProfile
        public virtual UserProfile GetOrCreateUserProfile(UserProfile template)
        {
            string key = BuildCacheKey(template.UserName, template.ApplicationName, template.IsAnonymous);

            var entity = this.Cache()[key] as UserProfile;

            // I. was UserProfile already cached
            if (entity.IsNull())
            {
                using (var command = CreateDbCommand())
                {
                    command.Connection.Open();
                    try
                    {
                        // II. is there UserProfile in storage
                        var list = Find(new UserProfileFilter
                        {
                            UserNameExact = template.UserName,
                            ApplicationNameExact = template.ApplicationName,
                            IsAnonymous = template.IsAnonymous,
                            MaxRowsPerPage = 1,
                        });
                        // III. take existing or create new
                        entity = list.IsNotEmpty()
                            ? list.First()  // existing
                            : Add(template); // new one

                        // IV. remeber it in cache
                        this.Cache().Insert(key, entity
                                , DbCache.Dependency
                                , DateTime.UtcNow.AddMinutes(5)
                                , System.Web.Caching.Cache.NoSlidingExpiration);
                    }
                    catch (Exception ex)
                    {
                        this.PublishFatal("UserProfile GetOrCreateUserProfile() failed", ex);
                    }
                    finally
                    {
                        command.Connection.Close();
                    }
                }

            }
            return entity;
        }
        #endregion Get UserProfile

        #region implement abstract Get
        /// <summary>
        /// Gets the filtered list of entities
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public override IEnumerable<UserProfile> Find(UserProfileFilter filter)
        {
            using (var command = CreateDbCommand())
            {

                // I. SELECT clause
                var commandText = CommandTextSelect;
                var maxRows = filter.MaxRowsPerPage * (filter.CurrentPage + 1);
                AddWithValue(command, "@MaxRows", maxRows);

                // II. WHERE clause
                string key;
                commandText += BuildWhere(filter, out key, command);

                // III. command text
                command.CommandText = commandText;
                command.Connection.Open();
                try
                {
                    var list = ExecuteReaderToList(command);

                    // IV. Base will apply order, and paging
                    return GetListByCriteria(filter, list);
                }
                finally
                {
                    command.Connection.Close();
                }
            }
        }
        /// <summary>
        /// Iterates the Find - all to find the entity with ID.Equals(id)
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public override UserProfile GetById(int id)
        {
            return Find(new UserProfileFilter { IdExact = id }).SingleOrDefault();
        }
        #endregion implement abstract Get

        #region implement abstract Set
        /// <summary>
        /// Creates INSERT statement with provided properties.
        /// Finally sets the ID
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public virtual UserProfile Add(UserProfile entity)
        {
            using (var command = CreateDbCommand())
            {
                command.Parameters.Clear();

                command.CommandText = CommandTextInsert;

                // append parmaters
                AddWithValue(command, "@UserName", entity.UserName);
                AddWithValue(command, "@ApplicationName", entity.ApplicationName);
                AddWithValue(command, "@IsAnonymous", entity.IsAnonymous);
                AddWithValue(command, "@LastActivityDate", entity.LastActivityDate);
                AddWithValue(command, "@LastUpdateDate", entity.LastUpdateDate);
                AddWithValue(command, "@Session", Serialize(entity.SerializedSession));

                var id = DbProviderFactory.CreateParameter();
                if (id.IsNull())
                {
                    throw new ConfigurationErrorsException("Cannot create DbParameter, incorrect connectionString '{0}' settings"
                        .FormatWith(ConnectionStringName));
                }

                id.DbType = DbType.Int32;
                id.ParameterName = "@UserProfileId";
                id.Direction = ParameterDirection.Output;

                command.Parameters.Add(id);

                command.Connection.Open();
                try
                {
                    command.ExecuteNonQuery();

                    entity.SetId((int)id.Value);
                    return entity;
                }
                finally
                {
                    command.Connection.Close();
                }
            }
        }

        /// <summary>
        /// Uses the entity.ID for where clause in the UPDATE statement
        /// </summary>
        /// <param name="entity"></param>
        public virtual UserProfile Update(UserProfile entity)
        {
            using (var command = CreateDbCommand())
            {
                command.Parameters.Clear();
                command.CommandText = CommandTextUpdate;

                AddWithValue(command, "@LastActivityDate", entity.LastActivityDate);
                AddWithValue(command, "@UserProfileId", entity.ID);

                if (entity.LastUpdateDate
                    >= entity.LastActivityDate)
                {
                    AddWithValue(command, "@LastUpdateDate", entity.LastUpdateDate);
                    AddWithValue(command, "@Session", Serialize(entity.SerializedSession));
                    command.CommandText = CommandTextUpdateFull;
                }

                string key = BuildCacheKey(entity.UserName, entity.ApplicationName, entity.IsAnonymous);

                command.Connection.Open();
                try
                {
                    command.ExecuteNonQuery();
                }
                finally
                {
                    command.Connection.Close();
                }

                this.Cache().Remove(key);
                return entity;
            }
        }

        /// <summary>
        /// Uses the entity.ID for where clause in the DELETE statement
        /// </summary>
        /// <param name="entity"></param>
        public virtual void Delete(UserProfile entity)
        {
            using (var command = CreateDbCommand())
            {
                command.Parameters.Clear();
                command.CommandText = CommandTextDelete;

                AddWithValue(command, "@UserProfileId", entity.ID);

                string key = BuildCacheKey(entity.UserName, entity.ApplicationName, entity.IsAnonymous);

                command.Connection.Open();
                try
                {
                    command.ExecuteNonQuery();
                }
                finally
                {
                    command.Connection.Close();
                }

                this.Cache().Remove(key);
            }
        }
        #endregion implement abstract Set

        #region Db utils , Serialize
        protected virtual string BuildCacheKey(string userName, string applicationName, bool isAnonymous)
        {
            return "UserProfileDao"
                + "|UNE:" + userName
                + "|IA:" + isAnonymous
                + "|ANE:" + applicationName;
        }
        protected virtual string BuildWhere(UserProfileFilter filter, out string uniqueCacheKey, IDbCommand command)
        {
            var commandWhere = new StringBuilder("$");
            var uniqueKey = new StringBuilder("UserProfileDao");

            if (filter.IdExact.Is())
            {
                commandWhere.Append("AND UserProfileId = @UserProfileId ");
                AddWithValue(command, "@UserProfileId", filter.IdExact.Value);
                uniqueKey.Append("|ID:" + filter.IdExact.Value);
            }
            // UserName
            if (filter.UserNameExact.Is())
            {
                commandWhere.Append("AND UserName = @UserNameExact ");
                AddWithValue(command, "@UserNameExact", filter.UserNameExact);
                uniqueKey.Append("|UNE:" + filter.UserNameExact);
            }
            if (filter.UserName.IsNotEmpty())
            {
                commandWhere.Append("AND UserName LIKE @UserName ");
                AddWithValue(command, "@UserName", filter.UserName.Replace("*", "%"));
                uniqueKey.Append("|UN:" + filter.UserName);
            }
            // ApplicationName
            if (filter.ApplicationNameExact.Is())
            {
                commandWhere.Append("AND ApplicationName = @ApplicationNameExact ");
                AddWithValue(command, "@ApplicationNameExact", filter.ApplicationNameExact);
                uniqueKey.Append("|ANE:" + filter.ApplicationNameExact);
            }
            if (filter.ApplicationName.IsNotEmpty())
            {
                commandWhere.Append("AND ApplicationName LIKE @ApplicationName ");
                AddWithValue(command, "@ApplicationName", filter.ApplicationName.Replace("*", "%"));
                uniqueKey.Append("|AN:" + filter.ApplicationName);
            }
            // IsAnonymous
            if (filter.IsAnonymous.Is())
            {
                commandWhere.Append("AND IsAnonymous = @IsAnonymous ");
                AddWithValue(command, "@IsAnonymous", filter.IsAnonymous.Value);
                uniqueKey.Append("|IA:" + filter.IsAnonymous.Value);
            }

            // LastActivity
            if (filter.LastActivityDate.Is())
            {
                commandWhere.Append("AND LastActivityDate = @LastActivityDate ");
                AddWithValue(command, "@LastActivityDate", filter.LastActivityDate);
                uniqueKey.Append("|LAD:" + filter.LastActivityDate.ToDisplay(formatProvider: CultureInfo.InvariantCulture));
            }
            if (filter.LastActivityDateFrom.Is())
            {
                commandWhere.Append("AND LastActivityDate >= @LastActivityDateFrom ");
                AddWithValue(command, "@LastActivityDateFrom", filter.LastActivityDateFrom);
                uniqueKey.Append("|LADF:" + filter.LastActivityDateFrom.ToDisplay(formatProvider: CultureInfo.InvariantCulture));
            }
            if (filter.LastActivityDateTo.Is())
            {
                commandWhere.Append("AND LastActivityDate <= @LastActivityDateTo ");
                AddWithValue(command, "@LastActivityDateTo", filter.LastActivityDateTo);
                uniqueKey.Append("|LADT:" + filter.LastActivityDateTo.ToDisplay(formatProvider: CultureInfo.InvariantCulture));
            }
            // LastUpdate
            if (filter.LastUpdateDate.Is())
            {
                commandWhere.Append("AND LastUpdateDate = @LastUpdateDate ");
                AddWithValue(command, "@LastUpdateDate", filter.LastUpdateDate);
                uniqueKey.Append("|LUD:" + filter.LastUpdateDate.ToDisplay(formatProvider: CultureInfo.InvariantCulture));
            }
            if (filter.LastUpdateDateFrom.Is())
            {
                commandWhere.Append("AND LastUpdateDate >= @LastUpdateDateFrom ");
                AddWithValue(command, "@LastUpdateDateFrom", filter.LastUpdateDateFrom);
                uniqueKey.Append("|LUDF:" + filter.LastUpdateDateFrom.ToDisplay(formatProvider: CultureInfo.InvariantCulture));
            }
            if (filter.LastUpdateDateTo.Is())
            {
                commandWhere.Append("AND LastUpdateDate <= @LastUpdateDateTo ");
                AddWithValue(command, "@LastUpdateDateTo", filter.LastUpdateDateTo);
                uniqueKey.Append("|LUDT:" + filter.LastUpdateDateTo.ToDisplay(formatProvider: CultureInfo.InvariantCulture));
            }

            uniqueCacheKey = uniqueKey.ToString();
            if (commandWhere.Length > 1) // not only starting "$"
            {
                return commandWhere.Replace("$AND", "WHERE").ToString();
            }

            return string.Empty;
        }
        protected virtual byte[] Serialize(IDictionary<string, object> source)
        {
            using (var stream = new MemoryStream())
            {
                BinaryFormatter.Serialize(stream, source);
                return stream.ToArray();
            }
        }
        protected virtual IDictionary<string, object> DeSerialize(byte[] source)
        {
            try
            {
                using (var stream = new MemoryStream())
                {
                    stream.Write(source, 0, source.Length);
                    stream.Position = 0;
                    return BinaryFormatter.Deserialize(stream) as IDictionary<string, object>;
                }
            }
            catch (Exception ex)
            {
                this.PublishFatal("Cannot deserialize persisted Session. Content could most likely contain some original data, which cannot be correctly re-instantiated.", ex);
                return new Dictionary<string, object>();
            }
        }
        protected virtual IDbCommand AddWithValue(IDbCommand command, string parameterName, object value, DbType? dbType = null)
        {
            // create parameter
            var param = DbProviderFactory.CreateParameter();

            // no parameter, no profile
            if (param.IsNull())
            {
                throw new ConfigurationErrorsException("Cannot create DbParameter, incorrect connectionString '{0}' settings"
                    .FormatWith(ConnectionStringName));
            }

            // fill parameter
            param.ParameterName = parameterName;
            param.Value = value;
            if (dbType.Is())
            {
                param.DbType = dbType.Value;
            }

            // finally add paramter
            command.Parameters.Add(param);

            return command;
        }
        protected virtual IDbCommand CreateDbCommand()
        {
            // factory creates connection
            var con = DbProviderFactory.CreateConnection();

            if (con.IsNull())
            {
                throw new ConfigurationErrorsException("Cannot create DbConnection, incorrect connectionString '{0}' settings"
                    .FormatWith(ConnectionStringName));
            }

            // inject connection string
            con.ConnectionString = ConnectionString;

            // create and check command
            var command = DbProviderFactory.CreateCommand();
            if (con.IsNull())
            {
                throw new ConfigurationErrorsException("Cannot create DbCommand, incorrect connectionString '{0}' settings"
                    .FormatWith(ConnectionStringName));
            }
            command.Connection = con;
            return command;
        }
        protected virtual DbProviderFactory CreateFactory()
        {
            // connection string element & value
            var connection = ConfigurationManager.ConnectionStrings[ConnectionStringName];
            if (connection.IsNull())
            {
                throw new ConfigurationErrorsException("ProfileProvider is missing ConnectionString setting. (name='ProfileProvider')");
            }
            ConnectionString = connection.ConnectionString;

            // factory dependent on provider e.g. System.Data.SqlClient
            var providerName = connection.ProviderName;
            var factory = DbProviderFactories.GetFactory(providerName);

            // no factory, no profile
            if (factory.IsNull())
            {
                throw new ConfigurationErrorsException("Cannot create DbProviderFactory, incorrect connectionString '{0}' settings. Provider Name found: '{1}'"
                    .FormatWith(ConnectionStringName, providerName));
            }
            return factory;
        }
        #endregion Db utils , Serialize

        #region CreateEntity
        /// <summary>
        /// Extend or adjust the DataRow transforamtion into 'UserProfile' instance.
        /// The base will try to fill the properties of the same names as the columns are
        /// </summary>
        /// <param name="dataReader"></param>
        /// <returns></returns>
        protected override UserProfile CreateEntity(IDataReader dataReader)
        {
            var entity = base.CreateEntity(dataReader);
            entity.SetId((int)dataReader["UserProfileId"]);
            var reader = dataReader as System.Data.SqlClient.SqlDataReader;
            if (reader.Is())
            {
                entity.SerializedSession = DeSerialize(reader.GetSqlBinary(dataReader.GetOrdinal("Session")).Value);
            }
            else
            {
                throw new NotImplementedException("Getting byte[] data type is by default implemented only for SqlClient. " +
                                                  "Provide your own conversion for any other provider");
            }
            return entity;
        }
        #endregion CreateEntity

        #region properties
        public virtual string ConnectionStringName
        {
            get { return _connectionStringName; }
            set { _connectionStringName = value; }
        }

        protected virtual DbProviderFactory DbProviderFactory
        {
            get { return _dbProviderFactory ?? (_dbProviderFactory = CreateFactory()); }
        }
        protected virtual string ConnectionString { get; set; }
        protected override IDbCommand DbCommand { get { throw new NotSupportedException("UserProfile does not support base.DbCommand functionality"); } }
        #endregion properties
    }
}